﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data.OleDb;
using System.Data;

namespace KaraokeManagementDAO
{
    public class DataProvider
    {
        #region ConnectionString
        //Xử lý chuỗi kết nối
        protected String connectionString;
        protected String ConnectionString
        {
            get
            {
                return ReadConnectionString("ConnectionString.xml");
            }
        }
        protected String ReadConnectionString(String file)
        {
            try
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(file);
                XmlElement root = doc.DocumentElement;
                String connectionString = root.InnerText;
                return connectionString;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion
        #region ExecuteQuery
        //Lấy dữ liệu
        protected DataTable ExecuteQuery(String query, List<OleDbParameter> OleDbParams)
        {
            DataTable dt = new DataTable();
            try
            {
                
                OleDbConnection connect = new OleDbConnection(ConnectionString);
                connect.Open();
                try
                {
                    OleDbCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    if (OleDbParams != null)
                    {
                        foreach (OleDbParameter param in OleDbParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    OleDbDataAdapter adapter = new OleDbDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(dt);
                }

                catch (OleDbException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        protected DataTable ExecuteQuery(String query)
        {
            return ExecuteQuery(query, null);
        }
        #endregion
        #region ExecuteNoneQuery
        //Thêm, xóa, sửa dữ liệu
        protected int ExecuteNoneQuery(String query, List<OleDbParameter> OleDbParams)
        {
            int n;
            try
            {
                OleDbConnection connect = new OleDbConnection(ConnectionString);
                connect.Open();
                try
                {
                    OleDbCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    if (OleDbParams != null)
                    {
                        foreach (OleDbParameter param in OleDbParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    n = command.ExecuteNonQuery();
                }

                catch (OleDbException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return n;
        }
        protected int ExecuteNoneQuery(String query)
        {
            return ExecuteNoneQuery(query, null);
        }
        #endregion
        #region ExecuteScalar
        //Lấy dữ liệu là 1 giá trị về
        protected object ExecuteScalar(String query, List<OleDbParameter> OleDbParams)
        {
            object obj = new object();
            try
            {
                OleDbConnection connect = new OleDbConnection(ConnectionString);
                connect.Open();
                try
                {
                    OleDbCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    if (OleDbParams != null)
                    {
                        foreach (OleDbParameter param in OleDbParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    obj = command.ExecuteScalar();
                }

                catch (OleDbException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return obj;
        }
        protected object ExecuteScalar(String query)
        {
            return ExecuteScalar(query, null);
        }
        #endregion
    }
}
